package com.AEWMS.Service;

import com.AEWMS.Dao.AttendWarnRank;
import com.AEWMS.Utlis.DbCon;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 * @author lhs
 * @create 2021/2/22 16:16
 */
public class AttendWarningRankService {

    private Connection conn = null ;
    private PreparedStatement pstmt = null ;

    public AttendWarningRankService() {
        DbCon dbcon;
        try {
            dbcon = new DbCon();
            this.conn = dbcon.getConnection();
        } catch (Exception e) {
            System.out.println("AttendWarningRankService 连接数据库失败");
            e.printStackTrace();
        }
    }

    public void closeCon(){
        try {
            this.conn.close();
        } catch (Exception e) {
            System.out.println("数据库连接关闭");
            e.printStackTrace();
        }
    }

    /**
     * 查找所有预警类型
     * @return 所有预警类型信息
     */
    public ArrayList<AttendWarnRank> findAllWarn(){
        ArrayList<AttendWarnRank> allWarnRankList = new ArrayList();//升级版数组，返回所有预警类型对象
        String sql = "SELECT * FROM attendance_warning";
        try {
            this.pstmt = conn.prepareStatement(sql);
            ResultSet rs = this.pstmt.executeQuery();
            while (rs.next()){
                AttendWarnRank s = new AttendWarnRank();//按行查找，为false时查询结束
                s.setWarningId(rs.getInt(1));
                s.setWarningRank(rs.getString(2));
                s.setWarningStandardStart(rs.getInt(3));
                s.setWarningStandardEnd(rs.getInt(4));
                allWarnRankList.add(s);//将查找到的行（每个预警类型对象）添加到数组
            }
        } catch (Exception e) {
            System.out.println("findAllWarn 失败");
            System.out.println(e);
        }
        return allWarnRankList;//输出包含所有预警类型对象的数组
    }

    /**
     * 查找预警类型
     * @param id 通过id
     * @return 返回一个预警类型对象
     */
    public AttendWarnRank findWarnRankById(String id){
        AttendWarnRank s = new AttendWarnRank();
        String sql = "SELECT * FROM attendance_warning WHERE warning_id = ?";
        try {
            this.pstmt = conn.prepareStatement(sql);//将sql语句放到数据库的准备对象
            this.pstmt.setString(1,id);//将传入id值替换问号放在SQL
            ResultSet rs = this.pstmt.executeQuery();//执行查询返回结果集
            while (rs.next()){
                s.setWarningId(rs.getInt(1));
                s.setWarningRank(rs.getString(2));
                s.setWarningStandardStart(rs.getInt(3));
                s.setWarningStandardEnd(rs.getInt(4));
            }
        }
        catch (Exception e) {
            System.out.println("findWarnRankById 失败");
            System.out.println(e);
        }
        return s;
    }

    /**
     * 添加预警类型
     * @return 一个预警类型对象
     */
    public boolean insertWarnRank(AttendWarnRank s){
        boolean flag = false;
        String sql = "INSERT INTO attendance_warning VALUES(?,?,?,?)";
        try {
            this.pstmt = conn.prepareStatement(sql);//sql语句已经在这个准备对象里，有问号就要用准备对象
            this.pstmt.setInt(1,s.getWarningId());
            this.pstmt.setString(2,s.getWarningRank());
            this.pstmt.setInt(3,s.getWarningStandardStart());
            this.pstmt.setInt(4,s.getWarningStandardEnd());
            int rs = this.pstmt.executeUpdate();//这里不用再传sql
            if ( rs == 1 ) {
                flag = true;
            }
        }
        catch (Exception e){
            System.out.println("insertWarnRank defeat");
            System.out.println(e);
        }
        return flag;
    }

    /**
     * 修改一个预警类型
     * @param s 一个预警类型对象
     * @return 是否成功
     */
    public boolean updateWarnRank(AttendWarnRank s) {
        boolean flag = false;
        String sql = "UPDATE attendance_warning SET warning_rank = ? ,warning_standard_start = ?,warning_standard_end = ? WHERE warning_id = ?";//这里的字段名要与数据库的一致
        try {
            this.pstmt = conn.prepareStatement(sql);
            this.pstmt.setString(1,s.getWarningRank());
            this.pstmt.setInt(2,s.getWarningStandardStart());
            this.pstmt.setInt(3,s.getWarningStandardEnd());
            this.pstmt.setInt(4,s.getWarningId());
            int rs = this.pstmt.executeUpdate();
            if ( rs == 1 ) {
                flag = true;
            }
        } catch (Exception e) {
            System.out.println("updateWarnRank 失败");
            System.out.println(e);
        }
        return flag;

    }

    /**
     * 删除一个预警类型
     * @param id 预警类型id
     * @return 是否成功
     */
    public boolean deleteWarnRankById(String id){
        boolean flag = false;
        String sql = "DELETE FROM attendance_warning WHERE warning_id = ?";//这里的字段名要与数据库的一致
        try {
            this.pstmt = conn.prepareStatement(sql);
            this.pstmt.setString(1,id);//这里的名字与dao.admin传进来的一致
            int rs = this.pstmt.executeUpdate();
            if ( rs == 1 ) {
                flag = true;
            }
        }
        catch (Exception e){
            System.out.println("deleteWarnRankById 失败");
            System.out.println(e);
        }
        return flag;
    }

}




